﻿namespace ManagerClinic.Core.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using Microsoft.Practices.EnterpriseLibrary.Common;
    using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
    using ManagerClinic.Core.Common;
    using ManagerClinic.Ultilities.Ultility;


    public class ThuPhiDichVuDataAccess
    {
        /// <summary>
        /// The method support insert new ThuPhiDichVuEntity into database
        /// </summary>
        /// <param name="entity">ThuPhiDichVuEntity</param>
        /// <returns>Identify of new ThuPhiDichVuEntity</returns>
        public bool InsertThuPhiDichVu(ThuPhiDichVuEntity obj)
        {
            try
            {
                string sql = "spThuPhiDichVu_Insert";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "MaThuPhi", DbType.String, Guid.NewGuid().ToString());
                db.AddInParameter(cmd, "MaPhieuDichVu", DbType.String, obj.MaPhieuDichVu);
                db.AddInParameter(cmd, "MaHoSo", DbType.String, obj.MaHoSo);
                db.AddInParameter(cmd, "NgayDenKham", DbType.DateTime, obj.NgayDenKham);
                db.AddInParameter(cmd, "MaDichVu", DbType.String, obj.MaDichVu);
                db.AddInParameter(cmd, "MaChiTieu", DbType.String, obj.MaChiTieu);
                db.AddInParameter(cmd, "TongTien", DbType.String, obj.TongTien);
                db.AddInParameter(cmd, "DaDongTien", DbType.Int32, obj.DaDongTien);
                db.AddInParameter(cmd, "MaNguoiDung", DbType.String, obj.MaNguoiDung);
                db.AddInParameter(cmd, "MaKhoaKham", DbType.String, obj.MaKhoaKham);
                db.AddInParameter(cmd, "MaPhongKham", DbType.String, obj.MaPhongKham);
                db.AddInParameter(cmd, "MaKhoaCanLamSang", DbType.String, obj.MaKhoaCanLamSang);
                db.AddInParameter(cmd, "MaPhongCanLamSang", DbType.String, obj.MaPhongCanLamSang);
                db.AddInParameter(cmd, "DaThucHien", DbType.Boolean, obj.DaThucHien);
                int result = (int)db.ExecuteNonQuery(cmd);

                return (result > 0) ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// The method support to update
        /// </summary>
        /// <param name="entity">ThuPhiDichVuEntity</param>
        /// <returns>true or false</returns>
        public bool EditThuPhiDichVu(ThuPhiDichVuEntity obj)
        {
            try
            {
                string sql = "spThuPhiDichVu_Update";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "MaThuPhi", DbType.String, obj.MaThuPhi);
                db.AddInParameter(cmd, "MaPhieuDichVu", DbType.String, obj.MaPhieuDichVu);
                db.AddInParameter(cmd, "MaHoSo", DbType.String, obj.MaHoSo);
                db.AddInParameter(cmd, "NgayDenKham", DbType.DateTime, obj.NgayDenKham);
                db.AddInParameter(cmd, "MaDichVu", DbType.String, obj.MaDichVu);
                db.AddInParameter(cmd, "MaChiTieu", DbType.String, obj.MaChiTieu);
                db.AddInParameter(cmd, "TongTien", DbType.String, obj.TongTien);
                db.AddInParameter(cmd, "DaDongTien", DbType.Int32, obj.DaDongTien);
                db.AddInParameter(cmd, "MaNguoiDung", DbType.String, obj.MaNguoiDung);
                db.AddInParameter(cmd, "MaKhoaKham", DbType.String, obj.MaKhoaKham);
                db.AddInParameter(cmd, "MaPhongKham", DbType.String, obj.MaPhongKham);
                db.AddInParameter(cmd, "MaKhoaCanLamSang", DbType.String, obj.MaKhoaCanLamSang);
                db.AddInParameter(cmd, "MaPhongCanLamSang", DbType.String, obj.MaPhongCanLamSang);
                db.AddInParameter(cmd, "DaThucHien", DbType.Boolean, obj.DaThucHien);
                int result = (int)db.ExecuteNonQuery(cmd);

                return (result > 0) ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// The method remove a ThuPhiDichVu
        /// </summary>
        /// <param name="Id">ThuPhiDichVu Id</param>
        /// <returns>true or false</returns>
        public bool DeleteThuPhiDichVu(string maThuPhi)
        {
            try
            {
                string sql = "spThuPhiDichVu_DeleteByID";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "MaThuPhi", DbType.String, maThuPhi);

                int result = (int)db.ExecuteNonQuery(cmd);
                return (result > 0) ? true : false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
       
        /// <summary>
        /// The method support to get a ThuPhiDichVuEntity
        /// </summary>
        /// <returns>ThuPhiDichVuEntity</returns>
        public ThuPhiDichVuEntity GetThuPhiDichVu(string maThuPhi)
        {
            try
            {
                ThuPhiDichVuEntity obj = null;
                string sql = "spThuPhiDichVu_SelectByID";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "MaThuPhi", DbType.String, maThuPhi);
                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        obj = new ThuPhiDichVuEntity();
                        obj.MaThuPhi = reader["MaThuPhi"].ToString();
                        obj.MaPhieuDichVu = reader["MaPhieuDichVu"].ToString();
                        obj.MaHoSo = reader["MaHoSo"].ToString();
                        obj.NgayDenKham = String.IsNullOrEmpty(reader["NgayDenKham"].ToString()) == true ? DateTime.MinValue : (DateTime)reader["NgayDenKham"];
                        obj.MaDichVu = reader["MaDichVu"].ToString();
                        obj.MaChiTieu = reader["MaChiTieu"].ToString();
                        obj.TongTien = Convert.ToDouble(reader["TongTien"]);
                        obj.DaDongTien = String.IsNullOrEmpty(reader["DaDongTien"].ToString()) == true ? 0 : (Int32)reader["DaDongTien"];
                        obj.MaNguoiDung = reader["MaNguoiDung"].ToString();
                        obj.MaKhoaKham = reader["MaKhoaKham"].ToString();
                        obj.MaPhongKham = reader["MaPhongKham"].ToString();
                        obj.MaKhoaCanLamSang = reader["MaKhoaCanLamSang"].ToString();
                        obj.MaPhongCanLamSang = reader["MaPhongCanLamSang"].ToString();
                        obj.DaThucHien = String.IsNullOrEmpty(reader["DaThucHien"].ToString()) == true ? false : (bool)reader["DaThucHien"];
                    }
                }

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// The method support to get all ThuPhiDichVuEntity
        /// </summary>
        /// <returns>list of ThuPhiDichVuEntity</returns>
        public List<ThuPhiDichVuEntity> GetThuPhiDichVuList()
        {
            try
            {
                List<ThuPhiDichVuEntity> list = new List<ThuPhiDichVuEntity>();
                ThuPhiDichVuEntity obj = null;
                string sql = "spThuPhiDichVu_SelectAll";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        obj = new ThuPhiDichVuEntity();
                        obj.MaThuPhi = reader["MaThuPhi"].ToString();
                        obj.MaPhieuDichVu = reader["MaPhieuDichVu"].ToString();
                        obj.MaHoSo = reader["MaHoSo"].ToString();
                        obj.NgayDenKham = String.IsNullOrEmpty(reader["NgayDenKham"].ToString()) == true ? DateTime.MinValue : (DateTime)reader["NgayDenKham"];
                        obj.MaDichVu = reader["MaDichVu"].ToString();
                        obj.MaChiTieu = reader["MaChiTieu"].ToString();
                        obj.TongTien = Convert.ToDouble(reader["TongTien"]);
                        obj.DaDongTien = String.IsNullOrEmpty(reader["DaDongTien"].ToString()) == true ? 0 : (Int32)reader["DaDongTien"];
                        obj.MaNguoiDung = reader["MaNguoiDung"].ToString();
                        obj.MaKhoaKham = reader["MaKhoaKham"].ToString();
                        obj.MaPhongKham = reader["MaPhongKham"].ToString();
                        obj.MaKhoaCanLamSang = reader["MaKhoaCanLamSang"].ToString();
                        obj.MaPhongCanLamSang = reader["MaPhongCanLamSang"].ToString();
                        obj.DaThucHien = String.IsNullOrEmpty(reader["DaThucHien"].ToString()) == true ? false : (bool)reader["DaThucHien"];
                        list.Add(obj);
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// The mothod support to get all ThuPhiDichVuEntity by MaPhieuDichVu, MaDichVu
        /// </summary>
        /// <param name="maPhieuDichVu"></param>
        /// <param name="maDichVu"></param>
        /// <returns></returns>
        public List<ThuPhiDichVuEntity> GetThuPhiDichVuListByMaPhieuDichVu_MaDichVu(string maPhieuDichVu, string maDichVu)
        {
            try
            {
                List<ThuPhiDichVuEntity> list = new List<ThuPhiDichVuEntity>();
                ThuPhiDichVuEntity obj = null;
                string sql = "spThuPhiDichVu_SelectByMaPhieuDichVu_MaDichVu";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "MaPhieuDichVu", DbType.String, maPhieuDichVu);
                db.AddInParameter(cmd, "MaDichVu", DbType.String, maDichVu);
                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        obj = new ThuPhiDichVuEntity();
                        obj.MaThuPhi = reader["MaThuPhi"].ToString();
                        obj.MaPhieuDichVu = reader["MaPhieuDichVu"].ToString();
                        obj.MaHoSo = reader["MaHoSo"].ToString();
                        obj.NgayDenKham = String.IsNullOrEmpty(reader["NgayDenKham"].ToString()) == true ? DateTime.MinValue : (DateTime)reader["NgayDenKham"];
                        obj.MaDichVu = reader["MaDichVu"].ToString();
                        obj.MaChiTieu = reader["MaChiTieu"].ToString();
                        obj.TongTien = Convert.ToDouble(reader["TongTien"]);
                        obj.DaDongTien = String.IsNullOrEmpty(reader["DaDongTien"].ToString()) == true ? 0 : (Int32)reader["DaDongTien"];
                        obj.MaNguoiDung = reader["MaNguoiDung"].ToString();
                        obj.MaKhoaKham = reader["MaKhoaKham"].ToString();
                        obj.MaPhongKham = reader["MaPhongKham"].ToString();
                        obj.MaKhoaCanLamSang = reader["MaKhoaCanLamSang"].ToString();
                        obj.MaPhongCanLamSang = reader["MaPhongCanLamSang"].ToString();
                        obj.DaThucHien = String.IsNullOrEmpty(reader["DaThucHien"].ToString()) == true ? false : (bool)reader["DaThucHien"];
                        list.Add(obj);
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// The method support to get all BenhNhan by NgayVaoKham and status DaDongTien
        /// </summary>
        /// <param name="ngayVaoKham">NgayVaoKham</param>
        /// <param name="daDongTien">Status DaDongTien</param>
        /// <returns></returns>
        public List<ThuPhiDichVuEntity> GetAllDanhSachBenhNhanThuPhi(DateTime ngayVaoKham, int daDongTien)
        {
            try
            {
                List<ThuPhiDichVuEntity> list = new List<ThuPhiDichVuEntity>();
                ThuPhiDichVuEntity obj = null;
                string sql = "";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "NgayVaoKham", DbType.DateTime, ngayVaoKham);
                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        obj = new ThuPhiDichVuEntity();
                        obj.MaBenhNhan = reader["MaBenhNhan"].ToString();
                        obj.TenBenhNhan = reader["TenBenhNhan"].ToString();
                        obj.NgaySinh = reader["NgaySinh"].ToString();
                        obj.ThangSinh = reader["ThangSinh"].ToString();
                        obj.NamSinh = String.IsNullOrEmpty(reader["NamSinh"].ToString()) == true ? 0 : (Int32)reader["NamSinh"];
                        obj.MaGioiTinh = reader["MaGioiTinh"].ToString();
                        obj.MaDanToc = reader["MaDanToc"].ToString();
                        obj.MaNgheNghiep = reader["MaNgheNghiep"].ToString();
                        obj.MaQuocGia = reader["MaQuocGia"].ToString();
                        obj.MaTinhThanh = reader["MaTinhThanh"].ToString();
                        obj.MaQuanHuyen = reader["MaQuanHuyen"].ToString();
                        obj.ThonPho = reader["ThonPho"].ToString();
                        obj.NoiLamViec = reader["NoiLamViec"].ToString();
                        obj.SoDienThoai = reader["SoDienThoai"].ToString();
                        obj.Email = reader["Email"].ToString();
                        obj.TenGioiTinh = reader["TenGioiTinh"].ToString();
                        obj.TenDanToc = reader["TenDanToc"].ToString();
                        obj.TenQuocGia = reader["TenQuocGia"].ToString();
                        obj.TenNgheNghiep = reader["TenNgheNghiep"].ToString();
                        obj.TenTinhThanh = reader["TenTinhThanh"].ToString();
                        obj.TenQuanHuyen = reader["TenQuanHuyen"].ToString();

                        obj.MaHoSo = reader["MaHoSo"].ToString();
                        obj.NgayVaoKham = String.IsNullOrEmpty(reader["NgayVaoKham"].ToString()) == true ? DateTime.MinValue : (DateTime)reader["NgayVaoKham"];
                        obj.TrangThaiPhieu = String.IsNullOrEmpty(reader["TrangThaiPhieu"].ToString()) == true ? 0 : (Int32)reader["TrangThaiPhieu"];
                        list.Add(obj);
                    }
                }
                return list;
            }
            catch (Exception ex) { LogExceptionUltilities.LogException("GetAllDanhSachBenhNhanThuPhi", ex.Message); throw ex; }
        }

        /// <summary>
        /// The method support to remove data by MaPhieuDichVu
        /// </summary>
        /// <param name="MaPhieuDichVu">maPhieuDichVu</param>
        /// <returns>true or false</returns>
        public bool DeleteThuPhiDichVuByMaPhieuDichVu(string maPhieuDichVu)
        {
            try
            {
                string sql = "[dbo].[spThuPhiDichVu_DeleteByMaPhieuDichVu]";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "MaPhieuDichVu", DbType.String, maPhieuDichVu);

                int result = (int)db.ExecuteNonQuery(cmd);
                return (result > 0) ? true : false;
            }
            catch (Exception ex) { LogExceptionUltilities.LogException("DeleteThuPhiDichVuByMaPhieuDichVu", ex.Message); throw ex; }
        }

        /// <summary>
        /// Hien thi tat ca cac chi tiet chi tiet trong bang thu phi theo ma ho so
        /// </summary>
        /// <param name="maHoSo">MaHoSo</param>
        /// <returns></returns>
        public IList<ThuPhiDichVuEntity> GetThuPhiDichVuByMaHoSo(string maHoSo)
        {
            try
            {
                List<ThuPhiDichVuEntity> list = new List<ThuPhiDichVuEntity>();
                ThuPhiDichVuEntity obj = null;
                string sql = "spThuPhiDichVu_SelectByMaHoSo";
                Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                DbCommand cmd = db.GetStoredProcCommand(sql);
                db.AddInParameter(cmd, "MaHoSo", DbType.String, maHoSo);
                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        obj = new ThuPhiDichVuEntity();
                        obj.MaPhieuDichVu = reader["MaPhieuDichVu"].ToString();
                        obj.MaHoSo = reader["MaHoSo"].ToString();
                        obj.MaChiTieu = reader["MaChiTieu"].ToString();
                        obj.TenChiTieu = reader["TenChiTieu"].ToString();
                        obj.DonVi = reader["DonVi"].ToString();
                        obj.TongTien = Convert.ToDouble(reader["TongTien"]);
                        list.Add(obj);
                    }
                }
                return list;
            }
            catch (Exception ex) { LogExceptionUltilities.LogException("GetThuPhiDichVuByMaHoSo", ex.Message); throw ex; }
        }
    }
}
